#! /bin/bash
export LANG=zh_CN.UTF-8
PRESTO_HOME=/export/server/presto/bin/presto


${PRESTO_HOME} --catalog hive --server 192.168.88.80:8090 --execute "
insert into intention_dws.dws_intention
with tmp as (
select
-- 指标字段
customer_id,
--维度字段
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
area,
itcast_subject_id,
itcast_subject_name,
itcast_school_id,
itcast_school_name,
origin_channel,
tdepart_id,
department_name,
-- 去重
row_number() over(partition by yearinfo, customer_id) as year_rn,
row_number() over(partition by yearinfo, monthinfo, customer_id) as month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, customer_id) as day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, customer_id) as hour_rn,

row_number() over(partition by yearinfo, O2O, customer_id) as o2o_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_id) as o2o_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_id) as o2o_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_id) as o2o_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, customer_id) as base_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, customer_id) as base_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, customer_id) as base_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, customer_id) as base_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, area, customer_id) as area_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, area, customer_id) as area_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, area, customer_id) as area_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, area, customer_id) as area_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, itcast_subject_id, customer_id) as sub_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, itcast_subject_id, customer_id) as sub_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_subject_id, customer_id) as sub_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_subject_id, customer_id) as sub_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, itcast_school_id, customer_id) as sch_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, itcast_school_id, customer_id) as sch_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_school_id, customer_id) as sch_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_school_id, customer_id) as sch_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, origin_channel, customer_id) as chan_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, origin_channel,customer_id) as chan_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, origin_channel,customer_id) as chan_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, origin_channel,customer_id) as chan_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, department_name,customer_id) as dep_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, department_name,customer_id) as dep_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, department_name,customer_id) as dep_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, department_name,customer_id) as dep_hour_rn

from intention_dwb.dwb_customer_intention)




SELECT
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
area,
itcast_subject_id,
itcast_subject_name,
itcast_school_id,
itcast_school_name,
origin_channel,
tdepart_id,
department_name,


case when grouping(hourinfo, department_name) = 0  then count(if(dep_hour_rn =1 and department_name is not null, customer_id, null)) else null end as       dep_hour_customer_count,
case when grouping(dayinfo, department_name) = 0   then count(if(dep_day_rn =1 and department_name is not null ,customer_id,null)) else null end as         dep_day_customer_count,
case when grouping(monthinfo, department_name) = 0 then count(if(dep_month_rn = 1 and department_name is not null ,customer_id,null)) else null end as      dep_month_customer_count,
case when grouping(yearinfo, department_name) = 0  then count(if(dep_year_rn = 1 and department_name is not null,customer_id,null)) else null end as       dep_year_customer_count,

case when grouping(hourinfo, origin_channel) = 0  then count(if(chan_hour_rn =1 and origin_channel is not null , customer_id   ,null)) else null end as     channel_hour_customer_count,
case when grouping(dayinfo, origin_channel) = 0   then count(if(chan_day_rn =1 and origin_channel is not null, customer_id     ,null))  else null end as    channel_day_customer_count,
case when grouping(monthinfo, origin_channel) = 0 then count(if(chan_month_rn = 1 and origin_channel is not null, customer_id ,null))  else null end as    channel_month_customer_count,
case when grouping(yearinfo, origin_channel) = 0  then count(if(chan_year_rn = 1 and origin_channel is not null , customer_id ,null))    else null end as  channel_year_customer_count,

case when grouping(hourinfo, itcast_school_id) = 0  then count(if(sch_hour_rn =1   ,customer_id ,null)) else null end as       sch_hour_customer_count,
case when grouping(dayinfo, itcast_school_id) = 0   then count(if(sch_day_rn =1    ,customer_id ,null)) else null end as       sch_day_customer_count,
case when grouping(monthinfo, itcast_school_id) = 0 then count(if(sch_month_rn = 1 ,customer_id ,null)) else null end as       sch_month_customer_count,
case when grouping(yearinfo, itcast_school_id) = 0  then count(if(sch_year_rn = 1  , customer_id,null))  else null end  as     sch_year_customer_count,

case when grouping(hourinfo, itcast_subject_id) = 0 then count(if(sub_hour_rn =1    ,customer_id ,null)) else null end as       sub_hour_customer_count,
case when grouping(dayinfo, itcast_subject_id) = 0  then count(if( sub_day_rn =1    ,customer_id ,null)) else null end as      sub_day_customer_count,
case when grouping(monthinfo, itcast_subject_id) = 0 then count(if( sub_month_rn = 1 ,customer_id ,null))  else null end as    sub_month_customer_count,
case when grouping(yearinfo, itcast_subject_id) = 0 then count(if( sub_year_rn = 1  , customer_id,null))  else null end as     sub_year_customer_count,

case when grouping(hourinfo, area) = 0  then count(if(area_hour_rn =1 and  area is not null  ,customer_id ,null)) else null end as area_hour_customer_count,
case when grouping(dayinfo, area) = 0   then count(if(area_day_rn =1  and area is not null  ,customer_id ,null))else null end  as area_day_customer_count,
case when grouping(monthinfo, area) = 0 then count(if( area_month_rn = 1 and area is not null,customer_id ,null)) else null end as area_month_customer_count,
case when grouping(yearinfo, area) = 0  then count(if( area_year_rn = 1 and area is not null, customer_id,null)) else null end as area_year_customer_count,

case when grouping(hourinfo, O2O, customer_mark) = 0   then count(if(base_hour_rn =1 and O2O is not null and  customer_mark is not null ,customer_id ,null)) else null end as base_hour_customer_count,
case when grouping(dayinfo, O2O, customer_mark) = 0    then count(if(base_day_rn =1  and O2O is not null and  customer_mark is not null ,customer_id ,null)) else null end as base_day_customer_count,
case when grouping(monthinfo, O2O, customer_mark) = 0  then count(if(base_month_rn = 1 and O2O is not null and  customer_mark is not null,customer_id ,null)) else null end as base_month_customer_count,
case when grouping(yearinfo, O2O, customer_mark) = 0   then count(if(base_year_rn = 1 and O2O is not null and  customer_mark is not null, customer_id,null)) else null end as base_year_customer_count,

case when grouping(hourinfo, O2O) = 0   then count(if(o2o_hour_rn =1 and O2O is not null  ,customer_id ,null)) else null end as  o2o_hour_customer_count,
case when grouping(dayinfo, O2O) = 0    then count(if(o2o_day_rn =1  and O2O is not null  ,customer_id ,null)) else null end as  o2o_day_customer_count,
case when grouping(monthinfo, O2O) = 0  then count(if(o2o_month_rn = 1 and O2O is not null ,customer_id ,null)) else null end as o2o_month_customer_count,
case when grouping(yearinfo, O2O) = 0   then count(if(o2o_year_rn = 1 and O2O is not null , customer_id,null)) else null end as  o2o_year_customer_count,

case when grouping(hourinfo) = 0  then count(if(hour_rn =1   ,customer_id ,null)) else null end as  hour_customer_count,
case when grouping(dayinfo) = 0   then count(if(day_rn =1    ,customer_id ,null)) else null end as  day_customer_count,
case when grouping(monthinfo) = 0 then count(if(month_rn = 1 ,customer_id ,null))  else null end as month_customer_count,
case when grouping(yearinfo) = 0  then count(if(year_rn = 1  , customer_id,null)) else null end as  year_customer_count,

-- 标记字段
case when grouping(department_name) = 0  then 'department'
when grouping(origin_channel)=0 then 'origin_channel'
when grouping(itcast_school_id)=0 then 'school'
when grouping(itcast_subject_id)=0 then 'subject'
when grouping(area)=0 then 'area'
when grouping(O2O,customer_mark)=0 then 'O2O_customer_mark'
when grouping(O2O)=0 then 'O2O'
else 'all' end as group_type,

case when grouping(hourinfo)=0 then 'hour'
when grouping(dayinfo)=0 then 'day'
when grouping(monthinfo)=0 then 'month'
when grouping(yearinfo)=0 then 'year'
else null end as time_type

from tmp



group by grouping sets(
/**
小时/每天/每月/每年
小时/每天/每月/每年 + 线上线下 新老学员
每天/每月/每年 + 线上线下 新老学员+地区 area
每天/每月/每年 + 线上线下 新老学员+学科
每天/每月/每年 + 线上线下 新老学员+校区
每天/每月/每年 + 线上线下 新老学员+来源渠道
每天/每月/每年 + 线上线下 新老学员+各咨询中心

O2O            STRING COMMENT 'online_offline标记',
customer_mark  STRING COMMENT 'new_old_customer标记',
 */
(yearinfo),
(yearinfo, monthinfo),
(yearinfo, monthinfo, dayinfo),
(yearinfo, monthinfo, dayinfo, hourinfo),

(yearinfo, O2O, customer_mark),
(yearinfo, monthinfo, O2O, customer_mark),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark),

(yearinfo, O2O),
(yearinfo, monthinfo, O2O),
(yearinfo, monthinfo, dayinfo, O2O),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O),

(yearinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, area),

(yearinfo, O2O, customer_mark, itcast_subject_id, itcast_subject_name),
(yearinfo, monthinfo, O2O, customer_mark, itcast_subject_id, itcast_subject_name),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_subject_id, itcast_subject_name),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_subject_id, itcast_subject_name),

(yearinfo, O2O, customer_mark, itcast_school_id, itcast_school_name),
(yearinfo, monthinfo, O2O, customer_mark, itcast_school_id, itcast_school_name),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_school_id, itcast_school_name),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_school_id, itcast_school_name),

(yearinfo, O2O, customer_mark, origin_channel),
(yearinfo, monthinfo, O2O, customer_mark, origin_channel),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, origin_channel),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, origin_channel),

(yearinfo, O2O, customer_mark, department_name, tdepart_id),
(yearinfo, monthinfo, O2O, customer_mark, department_name, tdepart_id),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, department_name, tdepart_id),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, department_name, tdepart_id)
);

--132843
select count(*) from intention_dws.dws_intention;

select * from intention_dws.dws_intention;

insert into intention_dws.dws_intention_without_dedup
with tmp as (
select
-- 指标字段
customer_id,
--维度字段
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
area,
itcast_subject_id,
itcast_subject_name,
itcast_school_id,
itcast_school_name,
origin_channel,
tdepart_id,
department_name
-- 去重
-- row_number() over(partition by yearinfo, customer_id) as year_rn,
-- row_number() over(partition by yearinfo, monthinfo, customer_id) as month_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, customer_id) as day_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, customer_id) as hour_rn,
--
-- row_number() over(partition by yearinfo, O2O, customer_mark) as base_year_rn,
-- row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark) as base_month_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark) as base_day_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark) as base_hour_rn,
--
-- row_number() over(partition by yearinfo, O2O, customer_mark, area) as area_year_rn,
-- row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, area) as area_month_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, area) as area_day_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, area) as area_hour_rn,
--
-- row_number() over(partition by yearinfo, O2O, customer_mark, itcast_subject_id) as sub_year_rn,
-- row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, itcast_subject_id) as sub_month_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_subject_id) as sub_day_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_subject_id) as sub_hour_rn,
--
-- row_number() over(partition by yearinfo, O2O, customer_mark, itcast_school_id) as sch_year_rn,
-- row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, itcast_school_id) as sch_month_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_school_id) as sch_day_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_school_id) as sch_hour_rn,
--
-- row_number() over(partition by yearinfo, O2O, customer_mark, origin_channel) as chan_year_rn,
-- row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, origin_channel) as chan_month_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, origin_channel) as chan_day_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, origin_channel) as chan_hour_rn,
--
-- row_number() over(partition by yearinfo, O2O, customer_mark, department_name) as dep_year_rn,
-- row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, department_name) as dep_month_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, department_name) as dep_day_rn,
-- row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, department_name) as dep_hour_rn

from intention_dwb.dwb_customer_intention)




SELECT
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
area,
itcast_subject_id,
itcast_subject_name,
itcast_school_id,
itcast_school_name,
origin_channel,
tdepart_id,
department_name,


case when grouping(hourinfo, department_name) = 0  then count(if(department_name is not null, customer_id, null)) else null end as       dep_hour_customer_count,
case when grouping(dayinfo, department_name) = 0   then count(if(department_name is not null, customer_id,null)) else null end as         dep_day_customer_count,
case when grouping(monthinfo, department_name) = 0 then count(if(department_name is not null, customer_id,null)) else null end as      dep_month_customer_count,
case when grouping(yearinfo, department_name) = 0  then count(if(department_name is not null,  customer_id,null)) else null end as       dep_year_customer_count,

case when grouping(hourinfo, origin_channel) = 0  then count(if(origin_channel is not null,  customer_id   ,null)) else null end as     channel_hour_customer_count,
case when grouping(dayinfo, origin_channel) = 0   then count(if(origin_channel is not null, customer_id     ,null))  else null end as    channel_day_customer_count,
case when grouping(monthinfo, origin_channel) = 0 then count(if(origin_channel is not null, customer_id ,null))  else null end as    channel_month_customer_count,
case when grouping(yearinfo, origin_channel) = 0  then count(if(origin_channel is not null,  customer_id ,null))    else null end as  channel_year_customer_count,

case when grouping(hourinfo, itcast_school_id) = 0  then count(if(itcast_school_id is not null ,customer_id ,null)) else null end as       sch_hour_customer_count,
case when grouping(dayinfo, itcast_school_id) = 0   then count(if(itcast_school_id is not null ,customer_id ,null)) else null end as       sch_day_customer_count,
case when grouping(monthinfo, itcast_school_id) = 0 then count(if(itcast_school_id is not null ,customer_id ,null)) else null end as       sch_month_customer_count,
case when grouping(yearinfo, itcast_school_id) = 0  then count(if(itcast_school_id is not null , customer_id,null))  else null end  as     sch_year_customer_count,

case when grouping(hourinfo, itcast_subject_id) = 0 then count(if(  itcast_subject_id is not null  ,customer_id ,null)) else null end as       sub_hour_customer_count,
case when grouping(dayinfo, itcast_subject_id) = 0  then count(if(  itcast_subject_id is not null ,customer_id ,null)) else null end as      sub_day_customer_count,
case when grouping(monthinfo, itcast_subject_id) = 0 then count(if( itcast_subject_id is not null ,customer_id ,null))  else null end as    sub_month_customer_count,
case when grouping(yearinfo, itcast_subject_id) = 0 then count(if(  itcast_subject_id is not null , customer_id,null))  else null end as     sub_year_customer_count,

case when grouping(hourinfo, area) = 0  then count(if( area is not null ,customer_id ,null)) else null end as area_hour_customer_count,
case when grouping(dayinfo, area) = 0   then count(if( area is not null ,customer_id ,null))else null end  as area_day_customer_count,
case when grouping(monthinfo, area) = 0 then count(if( area is not null,customer_id ,null)) else null end as area_month_customer_count,
case when grouping(yearinfo, area) = 0  then count(if( area is not null, customer_id,null)) else null end as area_year_customer_count,

case when grouping(hourinfo, O2O, customer_mark) = 0   then count(if(O2O is not null and customer_mark is not null,customer_id ,null)) else null end as base_hour_customer_count,
case when grouping(dayinfo, O2O, customer_mark) = 0    then count(if(O2O is not null and customer_mark is not null,customer_id ,null)) else null end as base_day_customer_count,
case when grouping(monthinfo, O2O, customer_mark) = 0  then count(if(O2O is not null and customer_mark is not null,customer_id ,null)) else null end as base_month_customer_count,
case when grouping(yearinfo, O2O, customer_mark) = 0   then count(if(O2O is not null and customer_mark is not null, customer_id,null)) else null end as base_year_customer_count,

case when grouping(hourinfo) = 0  then count(customer_id ) else null end as  hour_customer_count,
case when grouping(dayinfo) = 0   then count(customer_id ) else null end as  day_customer_count,
case when grouping(monthinfo) = 0 then count(customer_id )  else null end as month_customer_count,
case when grouping(yearinfo) = 0  then count( customer_id) else null end as  year_customer_count,

-- 标记字段
case when grouping(department_name) = 0  then 'department'
when grouping(origin_channel)=0 then 'origin_channel'
when grouping(itcast_school_id)=0 then 'school'
when grouping(itcast_subject_id)=0 then 'subject'
when grouping(area)=0 then 'area'
when grouping(O2O,customer_mark)=0 then 'O2O_customer_mark'
else 'all' end as group_type,

case when grouping(hourinfo)=0 then 'hour'
when grouping(dayinfo)=0 then 'day'
when grouping(monthinfo)=0 then 'month'
when grouping(yearinfo)=0 then 'year'
else null end as time_type

from tmp



group by grouping sets(
/**
小时/每天/每月/每年
小时/每天/每月/每年 + 线上线下 新老学员
每天/每月/每年 + 线上线下 新老学员+地区 area
每天/每月/每年 + 线上线下 新老学员+学科
每天/每月/每年 + 线上线下 新老学员+校区
每天/每月/每年 + 线上线下 新老学员+来源渠道
每天/每月/每年 + 线上线下 新老学员+各咨询中心

O2O            STRING COMMENT 'online_offline标记',
customer_mark  STRING COMMENT 'new_old_customer标记',
 */
(yearinfo),
(yearinfo, monthinfo),
(yearinfo, monthinfo, dayinfo),
(yearinfo, monthinfo, dayinfo, hourinfo),

(yearinfo, O2O, customer_mark),
(yearinfo, monthinfo, O2O, customer_mark),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark),

(yearinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, area),

(yearinfo, O2O, customer_mark, itcast_subject_id, itcast_subject_name),
(yearinfo, monthinfo, O2O, customer_mark, itcast_subject_id, itcast_subject_name),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_subject_id, itcast_subject_name),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_subject_id, itcast_subject_name),

(yearinfo, O2O, customer_mark, itcast_school_id, itcast_school_name),
(yearinfo, monthinfo, O2O, customer_mark, itcast_school_id, itcast_school_name),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_school_id, itcast_school_name),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_school_id, itcast_school_name),

(yearinfo, O2O, customer_mark, origin_channel),
(yearinfo, monthinfo, O2O, customer_mark, origin_channel),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, origin_channel),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, origin_channel),

(yearinfo, O2O, customer_mark, department_name, tdepart_id),
(yearinfo, monthinfo, O2O, customer_mark, department_name, tdepart_id),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, department_name, tdepart_id),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, department_name, tdepart_id)
);

--132843
select count(*) from intention_dws.dws_intention_without_dedup;

insert into intention_dws.dws_clue
with tmp as (
select
-- 指标字段
customer_id,
--维度字段
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
area,
itcast_subject_id,
itcast_subject,
itcast_school_id,
itcast_school,

-- 去重
row_number() over(partition by yearinfo, customer_id) as year_rn,
row_number() over(partition by yearinfo, monthinfo, customer_id) as month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, customer_id) as day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, customer_id) as hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark,customer_id) as base_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark,customer_id) as base_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark,customer_id) as base_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark,customer_id) as base_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, area,customer_id) as area_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, area,customer_id) as area_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, area,customer_id) as area_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, area,customer_id) as area_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, itcast_subject_id,customer_id) as sub_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, itcast_subject_id,customer_id) as sub_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_subject_id,customer_id) as sub_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_subject_id,customer_id) as sub_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, itcast_school_id,customer_id) as sch_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, itcast_school_id,customer_id) as sch_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_school_id,customer_id) as sch_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_school_id,customer_id) as sch_hour_rn


from intention_dwb.dwb_customer_clue)




SELECT
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
area,
itcast_subject_id,
itcast_subject,
itcast_school_id,
itcast_school,


case when grouping(hourinfo, itcast_school_id) = 0  then count(if(sch_hour_rn =1   ,customer_id ,null)) else null end as       sch_hour_customer_count,
case when grouping(dayinfo, itcast_school_id) = 0   then count(if(sch_day_rn =1    ,customer_id ,null)) else null end as       sch_day_customer_count,
case when grouping(monthinfo, itcast_school_id) = 0 then count(if(sch_month_rn = 1 ,customer_id ,null)) else null end as       sch_month_customer_count,
case when grouping(yearinfo, itcast_school_id) = 0  then count(if(sch_year_rn = 1  , customer_id,null))  else null end  as     sch_year_customer_count,

case when grouping(hourinfo, itcast_subject_id) = 0 then count(if(sub_hour_rn =1    ,customer_id ,null)) else null end as       sub_hour_customer_count,
case when grouping(dayinfo, itcast_subject_id) = 0  then count(if( sub_day_rn =1    ,customer_id ,null)) else null end as      sub_day_customer_count,
case when grouping(monthinfo, itcast_subject_id) = 0 then count(if( sub_month_rn = 1 ,customer_id ,null))  else null end as    sub_month_customer_count,
case when grouping(yearinfo, itcast_subject_id) = 0 then count(if( sub_year_rn = 1  , customer_id,null))  else null end as     sub_year_customer_count,

case when grouping(hourinfo, area) = 0  then count(if(area_hour_rn =1   ,customer_id ,null)) else null end as area_hour_customer_count,
case when grouping(dayinfo, area) = 0   then count(if(area_day_rn =1    ,customer_id ,null))else null end  as area_day_customer_count,
case when grouping(monthinfo, area) = 0 then count(if( area_month_rn = 1,customer_id ,null)) else null end as area_month_customer_count,
case when grouping(yearinfo, area) = 0  then count(if( area_year_rn = 1 , customer_id,null)) else null end as area_year_customer_count,

case when grouping(hourinfo, O2O, customer_mark) = 0   then count(if(base_hour_rn =1  ,customer_id ,null)) else null end as base_hour_customer_count,
case when grouping(dayinfo, O2O, customer_mark) = 0    then count(if(base_day_rn =1   ,customer_id ,null)) else null end as base_day_customer_count,
case when grouping(monthinfo, O2O, customer_mark) = 0  then count(if(base_month_rn = 1,customer_id ,null)) else null end as base_month_customer_count,
case when grouping(yearinfo, O2O, customer_mark) = 0   then count(if(base_year_rn = 1 , customer_id,null)) else null end as base_year_customer_count,

case when grouping(hourinfo) = 0  then count(if(hour_rn =1   ,customer_id ,null)) else null end as  hour_customer_count,
case when grouping(dayinfo) = 0   then count(if(day_rn =1    ,customer_id ,null)) else null end as  day_customer_count,
case when grouping(monthinfo) = 0 then count(if(month_rn = 1 ,customer_id ,null))  else null end as month_customer_count,
case when grouping(yearinfo) = 0  then count(if(year_rn = 1  , customer_id,null)) else null end as  year_customer_count,

-- 标记字段
case when grouping(itcast_school_id)=0 then 'school'
when grouping(itcast_subject_id)=0 then 'subject'
when grouping(area)=0 then 'area'
when grouping(O2O,customer_mark)=0 then 'O2O_customer_mark'
else 'all' end as group_type,

case when grouping(hourinfo)=0 then 'hour'
when grouping(dayinfo)=0 then 'day'
when grouping(monthinfo)=0 then 'month'
when grouping(yearinfo)=0 then 'year'
else null end as time_type

from tmp



group by grouping sets(
/**
小时/每天/每月/每年
小时/每天/每月/每年 + 线上线下 新老学员
每天/每月/每年 + 线上线下 新老学员+地区 area
每天/每月/每年 + 线上线下 新老学员+学科
每天/每月/每年 + 线上线下 新老学员+校区
每天/每月/每年 + 线上线下 新老学员+来源渠道
每天/每月/每年 + 线上线下 新老学员+各咨询中心

O2O            STRING COMMENT 'online_offline标记',
customer_mark  STRING COMMENT 'new_old_customer标记',
 */
(yearinfo),
(yearinfo, monthinfo),
(yearinfo, monthinfo, dayinfo),
(yearinfo, monthinfo, dayinfo, hourinfo),

(yearinfo, O2O, customer_mark),
(yearinfo, monthinfo, O2O, customer_mark),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark),

(yearinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, area),

(yearinfo, O2O, customer_mark, itcast_subject_id, itcast_subject),
(yearinfo, monthinfo, O2O, customer_mark, itcast_subject_id, itcast_subject),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_subject_id, itcast_subject),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_subject_id, itcast_subject),

(yearinfo, O2O, customer_mark, itcast_school_id, itcast_school),
(yearinfo, monthinfo, O2O, customer_mark, itcast_school_id, itcast_school),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_school_id, itcast_school),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_school_id, itcast_school)

);

select * from intention_dws.dws_clue_valid;


insert into intention_dws.dws_clue_valid
with tmp as (
select
-- 指标字段
customer_id,
--维度字段
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
area,
itcast_subject_id,
itcast_subject,
itcast_school_id,
itcast_school,

-- 去重
row_number() over(partition by yearinfo, customer_id) as year_rn,
row_number() over(partition by yearinfo, monthinfo, customer_id) as month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, customer_id) as day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, customer_id) as hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark,customer_id) as base_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark,customer_id) as base_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark,customer_id) as base_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark,customer_id) as base_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, area,customer_id) as area_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, area,customer_id) as area_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, area,customer_id) as area_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, area,customer_id) as area_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_mark, itcast_subject_id,customer_id) as sub_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, itcast_subject_id,customer_id) as sub_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_subject_id,customer_id) as sub_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_subject_id,customer_id) as sub_hour_rn,

row_number() over(partition by yearinfo, O2O, customer_id) as o2o_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O,customer_id) as o2o_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O,customer_id) as o2o_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O,customer_id) as o2o_hour_rn,


row_number() over(partition by yearinfo, O2O, customer_mark, itcast_school_id,customer_id) as sch_year_rn,
row_number() over(partition by yearinfo, monthinfo, O2O, customer_mark, itcast_school_id,customer_id) as sch_month_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_school_id,customer_id) as sch_day_rn,
row_number() over(partition by yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_school_id,customer_id) as sch_hour_rn


from intention_dwb.dwb_customer_clue where appeal_status=2 or appeal_status is null



)




SELECT
yearinfo,
monthinfo,
dayinfo,
hourinfo,
O2O,
customer_mark,
area,
itcast_subject_id,
itcast_subject,
itcast_school_id,
itcast_school,


case when grouping(hourinfo, itcast_school_id) = 0  then count(if(sch_hour_rn =1   ,customer_id ,null)) else null end as       sch_hour_customer_count,
case when grouping(dayinfo, itcast_school_id) = 0   then count(if(sch_day_rn =1    ,customer_id ,null)) else null end as       sch_day_customer_count,
case when grouping(monthinfo, itcast_school_id) = 0 then count(if(sch_month_rn = 1 ,customer_id ,null)) else null end as       sch_month_customer_count,
case when grouping(yearinfo, itcast_school_id) = 0  then count(if(sch_year_rn = 1  , customer_id,null))  else null end  as     sch_year_customer_count,

case when grouping(hourinfo, itcast_subject_id) = 0 then count(if(sub_hour_rn =1    ,customer_id ,null)) else null end as       sub_hour_customer_count,
case when grouping(dayinfo, itcast_subject_id) = 0  then count(if( sub_day_rn =1    ,customer_id ,null)) else null end as      sub_day_customer_count,
case when grouping(monthinfo, itcast_subject_id) = 0 then count(if( sub_month_rn = 1 ,customer_id ,null))  else null end as    sub_month_customer_count,
case when grouping(yearinfo, itcast_subject_id) = 0 then count(if( sub_year_rn = 1  , customer_id,null))  else null end as     sub_year_customer_count,

case when grouping(hourinfo, area) = 0  then count(if(area_hour_rn =1   ,customer_id ,null)) else null end as area_hour_customer_count,
case when grouping(dayinfo, area) = 0   then count(if(area_day_rn =1    ,customer_id ,null))else null end  as area_day_customer_count,
case when grouping(monthinfo, area) = 0 then count(if( area_month_rn = 1,customer_id ,null)) else null end as area_month_customer_count,
case when grouping(yearinfo, area) = 0  then count(if( area_year_rn = 1 , customer_id,null)) else null end as area_year_customer_count,

case when grouping(hourinfo, O2O, customer_mark) = 0   then count(if(base_hour_rn =1  ,customer_id ,null)) else null end as base_hour_customer_count,
case when grouping(dayinfo, O2O, customer_mark) = 0    then count(if(base_day_rn =1   ,customer_id ,null)) else null end as base_day_customer_count,
case when grouping(monthinfo, O2O, customer_mark) = 0  then count(if(base_month_rn = 1,customer_id ,null)) else null end as base_month_customer_count,
case when grouping(yearinfo, O2O, customer_mark) = 0   then count(if(base_year_rn = 1 , customer_id,null)) else null end as base_year_customer_count,

case when grouping(hourinfo, O2O) = 0   then count(if(o2o_hour_rn =1  ,customer_id ,null)) else null end as o2o_hour_customer_count,
case when grouping(dayinfo, O2O) = 0    then count(if(o2o_day_rn =1   ,customer_id ,null)) else null end as o2o_day_customer_count,
case when grouping(monthinfo, O2O) = 0  then count(if(o2o_month_rn = 1,customer_id ,null)) else null end as o2o_month_customer_count,
case when grouping(yearinfo, O2O) = 0   then count(if(o2o_year_rn = 1 , customer_id,null)) else null end as o2o_year_customer_count,


case when grouping(hourinfo) = 0  then count(if(hour_rn =1   ,customer_id ,null)) else null end as  hour_customer_count,
case when grouping(dayinfo) = 0   then count(if(day_rn =1    ,customer_id ,null)) else null end as  day_customer_count,
case when grouping(monthinfo) = 0 then count(if(month_rn = 1 ,customer_id ,null))  else null end as month_customer_count,
case when grouping(yearinfo) = 0  then count(if(year_rn = 1  , customer_id,null)) else null end as  year_customer_count,

-- 标记字段
case when grouping(itcast_school_id)=0 then 'school'
when grouping(itcast_subject_id)=0 then 'subject'
when grouping(area)=0 then 'area'
when grouping(O2O,customer_mark)=0 then 'O2O_customer_mark'
when grouping(O2O)=0 then 'O2O'
else 'all' end as group_type,

case when grouping(hourinfo)=0 then 'hour'
when grouping(dayinfo)=0 then 'day'
when grouping(monthinfo)=0 then 'month'
when grouping(yearinfo)=0 then 'year'
else null end as time_type

from tmp



group by grouping sets(
/**
小时/每天/每月/每年
小时/每天/每月/每年 + 线上线下 新老学员
每天/每月/每年 + 线上线下 新老学员+地区 area
每天/每月/每年 + 线上线下 新老学员+学科
每天/每月/每年 + 线上线下 新老学员+校区
每天/每月/每年 + 线上线下 新老学员+来源渠道
每天/每月/每年 + 线上线下 新老学员+各咨询中心

O2O            STRING COMMENT 'online_offline标记',
customer_mark  STRING COMMENT 'new_old_customer标记',
 */
(yearinfo),
(yearinfo, monthinfo),
(yearinfo, monthinfo, dayinfo),
(yearinfo, monthinfo, dayinfo, hourinfo),

(yearinfo, O2O),
(yearinfo, monthinfo, O2O),
(yearinfo, monthinfo, dayinfo, O2O),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O),

(yearinfo, O2O, customer_mark),
(yearinfo, monthinfo, O2O, customer_mark),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark),

(yearinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, area),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, area),

(yearinfo, O2O, customer_mark, itcast_subject_id, itcast_subject),
(yearinfo, monthinfo, O2O, customer_mark, itcast_subject_id, itcast_subject),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_subject_id, itcast_subject),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_subject_id, itcast_subject),

(yearinfo, O2O, customer_mark, itcast_school_id, itcast_school),
(yearinfo, monthinfo, O2O, customer_mark, itcast_school_id, itcast_school),
(yearinfo, monthinfo, dayinfo, O2O, customer_mark, itcast_school_id, itcast_school),
(yearinfo, monthinfo, dayinfo, hourinfo, O2O, customer_mark, itcast_school_id, itcast_school)

);

select * from intention_dws.dws_clue
select * from intention_dws.dws_clue_valid;
select * from intention_dwb.dwb_customer_clue where appeal_status is null;
select * from intention_dws.dws_intention where itcast_school_id is null;
select * from intention_dwb.dwb_customer_clue ;

select count(*) from (select * from intention_dwb.dwb_customer_clue where appeal_status is null) a join intention_dwd.fact_dwd_customer_relationship b on a.customer_relationship_first_id = b.id and appeal_status !=1;
select count(*) from intention_dwb.dwb_customer_clue a join intention_dwd.fact_dwd_customer_relationship b on a.customer_relationship_first_id = b.id;
select count(*) from intention_dwb.dwb_customer_clue a join intention_dwd.fact_dwd_customer_relationship b on a.customer_relationship_first_id = b.id;



-- 线索有效:
"